package com.zret.oasys.dao.impl;

import java.sql.Connection;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import com.zret.oasys.dao.LeaveDao;
import com.zret.oasys.dbtools.ITransaction;
import com.zret.oasys.dbtools.JDBCUtils;
import com.zret.oasys.dbtools.PageData;
import com.zret.oasys.entity.Leavedetails;
import com.zret.oasys.entity.Leavetable;

public class LeaveDaoImpl implements LeaveDao {
	
	//日期格式化
	SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	String date = "";
	/**
	 * 请假与申请表单的创建
	 */
	@Override
	public boolean addLeave(Leavetable leavetable, Leavedetails leavedetails) {

		// 请假表新增
		String sql1 = "insert into leavedetails values(null,?,?,?,?,?,?,?)";
		// 审批表新增
		String sql2 = "insert into leavetable values(?,?,?,?,?,?,?,?)";

		// 事务操作
		int result = (Integer) JDBCUtils.transaction(new ITransaction() {

			@Override
			public Object execute(Connection conn) {
				// TODO Auto-generated method stub
				int n1 = 0, n2 = 0;

				try {

					n1 = JDBCUtils.exUpdate(sql1, conn, leavedetails.getLeavenum(), leavedetails.getNode(),
							leavedetails.getApprid(), leavedetails.getAppcomment(), leavedetails.getGettime(),
							"1000-01-01 00:00:00", leavedetails.getStatus());
					
					n2 = JDBCUtils.exUpdate(sql2, conn, leavetable.getLeavenum(), leavetable.getEmpid(),
							leavetable.getStarttime(), leavetable.getLeavecomment(), leavetable.getEndtime(),
							leavetable.getLeavetype(), leavetable.getApptype(), leavetable.getComment());	

				} catch (Exception e) {
					e.printStackTrace();
					throw new RuntimeException("操作失败，事务回滚!", e);
				}

				return n1 + n2;
			}
		});

		return result > 1;
	}

	/**
	 * 查找所有审批单, 按照时间排序
	 */
	@Override
	public PageData<Leavedetails> getAllLeavedetails(int empid, int page, int pageSize) {
		// TODO Auto-generated method stub
		String sql = "select * from leavedetails where apprid = ? order by gettime desc";
		PageData<Leavedetails> pd = JDBCUtils.exQueryByPage(sql, Leavedetails.class, page, pageSize, empid);
		return pd;
	}

	/**
	 * 通过流水号获取请假表
	 */
	@Override
	public Leavetable getLeavetableByNum(String leavenum) {
		// TODO Auto-generated method stub
		String sql = "select * from leavetable where leavenum = ?";
		List<Leavetable> list = JDBCUtils.exQuery(sql, Leavetable.class, leavenum);
		return list.get(0);
	}

	/**
	 * 查找未审批的请假单
	 */
	@Override
	public PageData<Leavedetails> getNotApprovedLeavedetails(int empid, int page, int pageSize) {
		// TODO Auto-generated method stub
		String sql = "select * from leavedetails where apprid = ? and status = ? order by gettime desc";
		PageData<Leavedetails> pd = JDBCUtils.exQueryByPage(sql, Leavedetails.class, page, pageSize, empid, "false");
		return pd;
	}

	/**
	 * 找找已审批的请假单
	 */
	@Override
	public PageData<Leavedetails> getApprovedLeavedetails(int empid, int page, int pageSize) {
		// TODO Auto-generated method stub
		String sql = "select * from leavedetails where apprid = ? and status = ? order by gettime desc";
		PageData<Leavedetails> pd = JDBCUtils.exQueryByPage(sql, Leavedetails.class, page, pageSize, empid, "true");
		return pd;
	}

	/**
	 * 查全部, 需要根据员工姓名模糊查询
	 */
	@Override
	public PageData<Leavedetails> getAllLeavemsgByName(int empid, int page, int pageSize, String empname) {
		String sql = "select * from leavedetails where apprid = ? and leavenum in (select leavenum from leavetable where empid in (select empid from emp where empname like ?)) order by gettime asc";
		PageData<Leavedetails> pd = JDBCUtils.exQueryByPage(sql, Leavedetails.class, page, pageSize, empid,
				"%" + empname + "%");
		return pd;
	}

	/**
	 * 查已审批, 需要根据员工姓名模糊查询
	 */
	@Override
	public PageData<Leavedetails> getApprovalLeavemsgByName(int empid, int page, int pageSize, String empname) {
		String sql = "select * from leavedetails where apprid = ? and status = ? and leavenum in (select leavenum from leavetable where empid in (select empid from emp where empname like ?)) order by gettime asc";
		PageData<Leavedetails> pd = JDBCUtils.exQueryByPage(sql, Leavedetails.class, page, pageSize, empid, "已审批",
				"%" + empname + "%");
		return pd;
	}

	@Override
	public PageData<Leavedetails> getNotApprovalLeavemsgByName(int empid, int page, int pageSize, String empname) {
		String sql = "select * from leavedetails where apprid = ? and status = ? and leavenum in (select leavenum from leavetable where empid in (select empid from emp where empname like ?)) order by gettime asc";
		PageData<Leavedetails> pd = JDBCUtils.exQueryByPage(sql, Leavedetails.class, page, pageSize, empid, "待审批",
				"%" + empname + "%");
		return pd;
	}

	/**
	 * 审批通过的数据库操作(需要使用事务, 同时操作两张表)
	 */
	@Override
	public boolean leaveApprol(String leavenum, int detailid, String comment) {
		
		
		if (leavenum != null && comment != null) {
			// 请假表新增
			String sql1 = "update leavedetails set appcomment = ?, status = ?, apptime = ? where detailid = ?";
			// 审批表新增
			String sql2 = "update leavetable set comment = ?, apptype = ? where leavenum = ?";
			// 需要向打卡表插入一条请假记录
			// 事务操作
			date = simple.format(new Date());
			System.out.println(date);
			int result = (Integer) JDBCUtils.transaction(new ITransaction() {
				
				@Override
				public Object execute(Connection conn) {
					// TODO Auto-generated method stub
					int n1 = 0, n2 = 0;
					try {
						
						n1 = JDBCUtils.exUpdate(sql1, conn, comment, "已审批", date, detailid);	
						n2 = JDBCUtils.exUpdate(sql2, conn, comment, "已审批", leavenum);
																	
					} catch (Exception e) {
						e.printStackTrace();
						throw new RuntimeException("操作失败，事务回滚!", e);
					}

					return n1 + n2;
				}

			});
			return result > 0;
		}

		return false;
	}
	
	/**
	 * 查找个人信息
	 */
	@Override
	public PageData<Leavetable> getMyselfLeaveMsg(int empid, String searchtype, int page, int pageSize) { 
		// TODO Auto-generated method stub
		String sql = "";
		PageData<Leavetable> pd = null;
		if("all".equals(searchtype)) {
			sql = "select * from leavetable where empid = ? order by starttime asc";
			pd = (PageData<Leavetable>) JDBCUtils.exQueryByPage(sql, Leavetable.class, page, pageSize, empid);
		} else if("approval".equals(searchtype)) {
			sql = "select * from leavetable where empid = ? and apptype = ? order by starttime asc";
			pd = JDBCUtils.exQueryByPage(sql, Leavetable.class, page, pageSize, empid, "已审批");
		} else if("notapproval".equals(searchtype)) {
			sql = "select * from leavetable where empid = ? and apptype = ? order by starttime asc";
			pd = JDBCUtils.exQueryByPage(sql, Leavetable.class, page, pageSize, empid, "待审批");
		}
		
		return pd;
	}

	/**
	 * 通过流水号获取申请单
	 */
	@Override
	public Leavedetails getLeavedetailsByNum(String leavenum) {
		// TODO Auto-generated method stub
		String sql = "select * from leavedetails where leavenum = ?";
		List<Leavedetails> list = JDBCUtils.exQuery(sql, Leavedetails.class, leavenum);
		return list.get(0);
	}
}
